本篇同步發布於個人Blog: [PoEAA] Data Source Architectural Pattern - Table Data Gateway

1. What is Table Data Gateway

According to [PoEAA], this definition is "An object that acts as a Gateway to a database table. One instance handles all the rows in the table."

Figure 1. Table Data Gateway (From PoEAA Page)

1.1 How it works

Every function provided by a gateway maps a SQL query to the database. The functions are usually finding/updating/deleting. 

1.2 When to use it

1. It does not usually work with Domain Model pattern. Domain Model pattern fits Data Mapper pattern.
2. Table Module pattern very fits this pattern.
3. It fits Transaction Script pattern.
4. It hides the table schema and provides functions that are implemented by SQL Queries or Stored Procedures.

2. Pattern Practice: The Person Management

As Figure 1 shows, there is a person table that has a primary key id and 3 attributes (lastName, firstName and numberOfDependents).

2.1 Implementation by C# & SQLite

This pattern is implemented by C# based on the content of Chapter 10 Data Source Architectural Patterns - Table Data Gateway of PoEAA. The database is SQLite.

By Martin's implementation, it contains ADO.NET Reader version and ADO.NET DataSet version. The following sections show both versions.

2.2 ADO.NET Reader Version

2.2.1 PersonGateway

This gateway creates Find/Insert/Update/Delete basic functions to manipulate person table.

class PersonGateway
{
	public IDataReader FindAll()
	{
		string sql = "select * from person";
		var conn = DbManager.CreateConnection();
		conn.Open();
		return new SQLiteCommand(sql, conn).ExecuteReader();
	}

	public IDataReader FindWithLastName(string lastName)
	{
		string sql = "select * from person where lastname = $lastname";
		var conn = DbManager.CreateConnection();
		conn.Open();
		IDbCommand comm = new SQLiteCommand(sql, conn);
		comm.Parameters.Add(new SQLiteParameter("$lastname", lastName));

		return comm.ExecuteReader();
	}

	public IDataReader FindWhere(string whereClause)
	{
		string sql = $"select * from person where {whereClause}";
		var conn = DbManager.CreateConnection();
		conn.Open();
		return new SQLiteCommand(sql, conn).ExecuteReader();
	}

	public object[] FindRow(long key)
	{
		string sql = "select * from person where id = $id";
		using var conn = DbManager.CreateConnection();
		conn.Open();
		using IDbCommand comm = new SQLiteCommand(sql, conn);
		comm.Parameters.Add(new SQLiteParameter("$id", key));
		using IDataReader reader = comm.ExecuteReader();
		reader.Read();
		object[] result = new object[reader.FieldCount];
		reader.GetValues(result);
		return result;
	}

	public void Update(long key, string lastName, string firstName, int numberOfDependents)
	{
		string sql =
			@"Update person SET lastname = $lastname, firstname = $firstname, numberOfDependents = $numberOfDependents
						WHERE id = $id";
		using var conn = DbManager.CreateConnection();
		conn.Open();
		using IDbCommand comm = new SQLiteCommand(sql, conn);
		comm.Parameters.Add(new SQLiteParameter("$lastname", lastName));
		comm.Parameters.Add(new SQLiteParameter("$firstname", firstName));
		comm.Parameters.Add(new SQLiteParameter("$numberOfDependents", numberOfDependents));
		comm.Parameters.Add(new SQLiteParameter("$id", key));
		comm.ExecuteNonQuery();
	}

	public long Insert(string lastName, string firstName, int numberOfDependents)
	{
		string sql =
			@"INSERT INTO person VALUES ($id, $lastname, $firstname, $numberOfDependents)";
		long key = GetNextId();
		using var conn = DbManager.CreateConnection();
		conn.Open();
		using IDbCommand comm = new SQLiteCommand(sql, conn);
		comm.Parameters.Add(new SQLiteParameter("$id", key));
		comm.Parameters.Add(new SQLiteParameter("$lastname", lastName));
		comm.Parameters.Add(new SQLiteParameter("$firstname", firstName));
		comm.Parameters.Add(new SQLiteParameter("$numberOfDependents", numberOfDependents));
		comm.ExecuteNonQuery();
		return key;
	}

	public void Delete(long key)
	{
		string sql = "DELETE FROM person WHERE id = $id";
		using var conn = DbManager.CreateConnection();
		conn.Open();
		IDbCommand comm = new SQLiteCommand(sql, conn);
		comm.Parameters.Add(new SQLiteParameter("$id", key));
		comm.ExecuteNonQuery();
	}

	private long GetNextId()
	{
		string sql = "SELECT max(id) as curId from person";
		using var conn = DbManager.CreateConnection();
		conn.Open();
		using IDbCommand comm = new SQLiteCommand(sql, conn);
		using IDataReader reader = comm.ExecuteReader();
		bool hasResult = reader.Read();
		if (hasResult)
		{
			return ((long)reader["curId"] + 1);
		}
		else
		{
			return 1;
		}
	}
}

2.3 ADO.NET DataSet Version

Use a DataSetHolder class to hold a DataSet and a DataAdapter for a DataGateway. This definition is for generality as the following Figure 2 shows.

Figure 2. Class diagram of data-set-oriented gateway and the supporting data holder

2.3.1 DataSetHolder class

This class holds a DataSet and a dictionary. The dictionary's key is the table name, its value mapped to a DataAdapter instance.

class DataSetHolder
{
	public DataSet Data = new DataSet();
	private readonly Dictionary<string, SQLiteDataAdapter> _dataAdapters = new Dictionary<string, SQLiteDataAdapter>();

	public void FillData(string query, string tableName)
	{
		if (_dataAdapters.ContainsKey(tableName))
		{
			throw new MultipleLoadException();
		}

		var conn = DbManager.CreateConnection();
		conn.Open();
		SQLiteDataAdapter da = new SQLiteDataAdapter(query, conn);
		SQLiteCommandBuilder builder = new SQLiteCommandBuilder(da);
		da.Fill(Data, tableName);
		_dataAdapters.Add(tableName, da);
	}

	public void Update()
	{
		foreach (string table in _dataAdapters.Keys)
		{
			(_dataAdapters[table]).Update(Data, table);
		}
	}

	public DataTable this[string tableName] => Data.Tables[tableName];
}

2.3.2 DataGateway & PersonGateway

DataGateway is the base class and provides the common functions to child classes. DataGateway exposes a DataSet and a DataTable to clients. The child PersonGateway implements the table name "person" and creates a new Insert function to add person record.

abstract class DataGateway
{
	public abstract string TableName { get; }
	public DataSetHolder Holder;

	public DataSet Data => Holder.Data;

	public abstract DataTable Table { get; }

	protected DataGateway()
	{
		Holder = new DataSetHolder();
	}

	protected DataGateway(DataSetHolder holder)
	{
		this.Holder = holder;
	}

	public void LoadAll()
	{
		string commandString = $"select * from {TableName}";
		Holder.FillData(commandString, TableName);
	}

	public void LoadWhere(string whereClause)
	{
		string commandString = $"select * from {TableName} where {whereClause}";
		Holder.FillData(commandString, TableName);
	}
}


class PersonGateway : DataGateway
{
	public override string TableName => "person";

	public override DataTable Table => Data.Tables[TableName];

	public PersonGateway() : base()
	{

	}

	public PersonGateway(DataSetHolder holder) : base(holder)
	{

	}

	public DataRow this[long key]
	{
		get
		{
			string filter = $"id = {key}";
			return Table.Select(filter)[0];
		}
	}

	public long Insert(string lastName, string firstName, int numberOfDependents)
	{
		long key = GetNextId();
		DataRow newRow = Table.NewRow();
		newRow["id"] = key;
		newRow["lastname"] = lastName;
		newRow["firstname"] = firstName;
		newRow["numberOfDependents"] = numberOfDependents;
		Table.Rows.Add(newRow);

		return key;
	}

	private long GetNextId()
	{
		var result = Table.Compute("max([id])", string.Empty);
		if (result != System.DBNull.Value)
		{
			return ((int)result + 1);
		}
		else
		{
			return 1;
		}
	}
}

2.4 Demo

Create a console program and create 3 Persons in SQLite, the person records:

Figure 3. Person Records

The program first executes Reader version functions and second executes DataSet version functions.

As the following code:

class Program
{
	static void Main(string[] args)
	{
		RunReaderVersionExample();
		RunDataTableVersionExample();
	}

	private static void RunDataTableVersionExample()
	{
		InitializeData();

		Console.WriteLine("Start RunDataTableVersionExample");
		Console.WriteLine("Function: Get all persons");
		var gateway = new DataTableVersion.PersonGateway();
		gateway.LoadAll();
		var allPersons = gateway.Table.Rows;
		PrintPersonsRowData(allPersons);

		Console.WriteLine("Function: Get person by id = 2");
		var onePerson = gateway[2];
		PrintPersonRowData(onePerson);

		Console.WriteLine("Function: Update person by id = 2");
		onePerson["lastname"] = "Jackson";
		onePerson["firstname"] = "Michael";
		onePerson["numberOfDependents"] = 100;
		gateway.Holder.Update();
		Console.WriteLine("Function: Get person by id = 2");
		var updatedPerson = gateway[2];
		PrintPersonRowData(updatedPerson);

		Console.WriteLine("Function: Insert a person");
		gateway.Insert("Skinner", "Neil", 3);
		gateway.Holder.Update();
		Console.WriteLine("Function: Get all persons");
		allPersons = gateway.Table.Rows;
		PrintPersonsRowData(allPersons);

		Console.WriteLine("Function: Get persons by numberOfDependents > 10");
		gateway = new DataTableVersion.PersonGateway();
		gateway.LoadWhere("numberOfDependents > 10");
		var findPersons = gateway.Table.Rows;
		PrintPersonsRowData(findPersons);

		Console.WriteLine("Function: Delete person by id = 1");
		gateway = new DataTableVersion.PersonGateway();
		gateway.LoadAll();
		var deletedRow = gateway[1];
		deletedRow.Delete();
		gateway.Holder.Update();

		Console.WriteLine("Function: Get all persons");
		allPersons = gateway.Table.Rows;
		PrintPersonsRowData(allPersons);

		Console.WriteLine("End RunDataTableVersionExample");
	}

	private static void RunReaderVersionExample()
	{
		InitializeData();

		Console.WriteLine("Start RunReaderVersionExample");
		Console.WriteLine("Function: Get all persons");
		var gateway = new ReaderVersion.PersonGateway();
		var allPersons = gateway.FindAll();
		PrintPersonsRowData(allPersons);
		allPersons.Close();

		Console.WriteLine("Function: Get person by id = 2");
		var onePerson = gateway.FindRow(2);
		PrintPersonRowData(onePerson);

		Console.WriteLine("Function: Update person by id = 2");
		gateway.Update(2, "Jackson", "Michael", 100);
		Console.WriteLine("Function: Get person by id = 2");
		var updatedPerson = gateway.FindRow(2);
		PrintPersonRowData(updatedPerson);

		Console.WriteLine("Function: Insert a person");
		gateway.Insert("Skinner", "Neil", 3);

		Console.WriteLine("Function: Get all persons");
		allPersons = gateway.FindAll();
		PrintPersonsRowData(allPersons);
		allPersons.Close();

		Console.WriteLine("Function: Get persons by numberOfDependents > 10");
		var findPersons = gateway.FindWhere("numberOfDependents > 10");
		PrintPersonsRowData(findPersons);
		findPersons.Close();

		Console.WriteLine("Function: Delete person by id = 1");
		gateway.Delete(1);

		Console.WriteLine("Function: Get all persons");
		allPersons = gateway.FindAll();
		PrintPersonsRowData(allPersons);
		allPersons.Close();

		Console.WriteLine("End RunReaderVersionExample");
	}

	private static void PrintPersonRowData(object[] columns)
	{
		Console.WriteLine($"id: {columns[0]}, lastname: {columns[1]}, firstname: {columns[2]}, numberOfDependents: {columns[3]}");
	}

	private static void PrintPersonRowData(DataRow columns)
	{
		Console.WriteLine($"id: {columns[0]}, lastname: {columns[1]}, firstname: {columns[2]}, numberOfDependents: {columns[3]}");
	}

	private static void PrintPersonsRowData(DataRowCollection dataRows)
	{
		foreach (DataRow row in dataRows)
		{
			Console.WriteLine($"id: {row["id"]}, lastname: {row["lastname"]}, firstname: {row["firstname"]}, numberOfDependents: {row["numberOfDependents"]}");
		}
	}

	private static void PrintPersonsRowData(IDataReader reader)
	{
		while (reader.Read())
		{
			Console.WriteLine($"id: {reader["id"]}, lastname: {reader["lastname"]}, firstname: {reader["firstname"]}, numberOfDependents: {reader["numberOfDependents"]}");
		}
	}

	private static void InitializeData()
	{
		using (var connection = DbManager.CreateConnection())
		{
			connection.Open();

			using (var command = connection.CreateCommand())
			{
				command.CommandText =
						@"
					DROP TABLE IF EXISTS person;
				";
					command.ExecuteNonQuery();


					command.CommandText =
						@"
					CREATE TABLE person (Id int primary key, lastname TEXT, firstname TEXT, numberOfDependents int);
				";
					command.ExecuteNonQuery();

					command.CommandText =
						@"
				   
				INSERT INTO person
					VALUES (1, 'Sean', 'Reid', 5);

				INSERT INTO person
					VALUES (2, 'Madeleine', 'Lyman', 13);

				INSERT INTO person
					VALUES (3, 'Oliver', 'Wright', 66);
				";
				command.ExecuteNonQuery();
			}
				
		}
	}
}

The console shows:

3. Conclusions

"Table Data Gateway" is a simpler data source architectural pattern. We can create a gateway to map a table(or a view/a store procedure) even map to whole tables. If the application is not complex, Table Data Gateway is a good choice.

The above sample code is uploaded to this Github Repository.

For next article I will write Row Data Gateway pattern according to Chapter 10 Data Source Architectural Pattern - Table Data Gateway of PoEAA.

4. References

Patterns of Enterprise Application Architecture Book(Amazon)